--    Program: 		CAPARIMP_MEMO_LINE.sql
--    Description:  Import AR Memo Line information 
--    Revision:     1st     
--    Parameter: 	

set doc off;      
set verify off;
set serveroutput on size 1000000;
--whenever sqlerror exit failure rollback;

declare   
   l_processed_count_1		number;
   l_processed_count_2		number;
   l_rejected_count_1 		number;
   l_rejected_count_2 		number;
   l_err_msg_1				varchar2(2000);
   l_err_msg_2				varchar2(2000);
   l_err_msg_tmp_1			varchar2(2000); 
   l_err_msg_tmp_2			varchar2(2000);
   
   l_memo_line_id 			number;
   
   cursor cur_line_b is
      select * from CRST_AR_MEMO_LINES_ALL_B
      for update;
      
   cursor cur_line_tl(p_org_id number, p_memo_line_id number) is
      select * from CRST_AR_MEMO_LINES_ALL_TL
      where org_id = p_org_id
      and memo_line_id = p_memo_line_id;   

begin
   l_processed_count_1 := 0;
   l_rejected_count_1  := 0;
   l_err_msg_1 := '';
   
   l_processed_count_2 := 0;
   l_rejected_count_2  := 0;
   l_err_msg_2 := '';
   
   for cur1 in cur_line_b loop   	    
   	 l_err_msg_tmp_1 := '';
   	 
   	 select AR_MEMO_LINES_S.NEXTVAL
   	 into l_memo_line_id
   	 from dual;
   	 
   	 update CRST_AR_MEMO_LINES_ALL_B
   	 set    memo_line_id_n = l_memo_line_id,
   	        org_id_n = CAPBRIMP_MAPPING.GetOrgMapping(cur1.ORG_ID)
   	 where  memo_line_id = cur1.MEMO_LINE_ID
   	 and    org_id = cur1.ORG_ID;       
   	 
   	 begin
       -- import data
       insert into AR_MEMO_LINES_ALL_B 
       (
         MEMO_LINE_ID               ,
         ORG_ID                     ,
         SET_OF_BOOKS_ID            ,
         LINE_TYPE                  ,
         START_DATE                 ,
         LAST_UPDATE_DATE           ,
         LAST_UPDATED_BY            ,
         CREATED_BY                 ,
         CREATION_DATE              ,
         GL_ID_REV                  ,
         TAX_CODE                   ,
         UOM_CODE                   ,
         UNIT_STD_PRICE             ,
         INVOICING_RULE_ID          ,
         ACCOUNTING_RULE_ID         ,
         END_DATE                   ,
         ATTRIBUTE_CATEGORY         ,
         ATTRIBUTE1                 ,
         ATTRIBUTE2                 ,
         ATTRIBUTE3                 ,
         ATTRIBUTE4                 ,
         ATTRIBUTE5                 ,
         ATTRIBUTE6                 ,
         ATTRIBUTE7                 ,
         ATTRIBUTE8                 ,
         ATTRIBUTE9                 ,
         ATTRIBUTE10                ,
         ATTRIBUTE11                ,
         ATTRIBUTE12                ,
         ATTRIBUTE13                ,
         ATTRIBUTE14                ,
         ATTRIBUTE15                ,
         GLOBAL_ATTRIBUTE_CATEGORY  ,
         GLOBAL_ATTRIBUTE1          ,
         GLOBAL_ATTRIBUTE2          ,
         GLOBAL_ATTRIBUTE3          ,
         GLOBAL_ATTRIBUTE4          ,
         GLOBAL_ATTRIBUTE5          ,
         GLOBAL_ATTRIBUTE6          ,
         GLOBAL_ATTRIBUTE7          ,
         GLOBAL_ATTRIBUTE8          ,
         GLOBAL_ATTRIBUTE9          ,
         GLOBAL_ATTRIBUTE10         ,
         GLOBAL_ATTRIBUTE11         ,
         GLOBAL_ATTRIBUTE12         ,
         GLOBAL_ATTRIBUTE13         ,
         GLOBAL_ATTRIBUTE14         ,
         GLOBAL_ATTRIBUTE15         ,
         GLOBAL_ATTRIBUTE16         ,
         GLOBAL_ATTRIBUTE17         ,
         GLOBAL_ATTRIBUTE18         ,
         GLOBAL_ATTRIBUTE19         ,
         GLOBAL_ATTRIBUTE20         ,
         LAST_UPDATE_LOGIN
       )
       values
       (
         l_memo_line_id                  ,
         CAPBRIMP_MAPPING.GetOrgMapping(cur1.ORG_ID),
         CAPBRIMP_MAPPING.GetSOBIDMapping(cur1.SET_OF_BOOKS_ID),
         cur1.LINE_TYPE                  ,
         cur1.START_DATE                 ,
         cur1.LAST_UPDATE_DATE           ,
         CAPBRIMP_MAPPING.GetUserMapping(cur1.LAST_UPDATED_BY),
         CAPBRIMP_MAPPING.GetUserMapping(cur1.CREATED_BY),  
         cur1.CREATION_DATE              ,
         CAPBRIMP_MAPPING.GetCCIDMapping(cur1.GL_ID_REV),
         cur1.TAX_CODE                   ,
         cur1.UOM_CODE                   ,
         cur1.UNIT_STD_PRICE             ,
         cur1.INVOICING_RULE_ID          ,
         cur1.ACCOUNTING_RULE_ID         ,
         cur1.END_DATE                   ,
         cur1.ATTRIBUTE_CATEGORY         ,
         cur1.ATTRIBUTE1                 ,
         cur1.ATTRIBUTE2                 ,
         cur1.ATTRIBUTE3                 ,
         cur1.ATTRIBUTE4                 ,
         cur1.ATTRIBUTE5                 ,
         cur1.ATTRIBUTE6                 ,
         cur1.ATTRIBUTE7                 ,
         cur1.ATTRIBUTE8                 ,
         cur1.ATTRIBUTE9                 ,
         cur1.ATTRIBUTE10                ,
         cur1.ATTRIBUTE11                ,
         cur1.ATTRIBUTE12                ,
         cur1.ATTRIBUTE13                ,
         cur1.ATTRIBUTE14                ,
         cur1.ATTRIBUTE15                ,
         cur1.GLOBAL_ATTRIBUTE_CATEGORY  ,
         cur1.GLOBAL_ATTRIBUTE1          ,
         cur1.GLOBAL_ATTRIBUTE2          ,
         cur1.GLOBAL_ATTRIBUTE3          ,
         cur1.GLOBAL_ATTRIBUTE4          ,
         cur1.GLOBAL_ATTRIBUTE5          ,
         cur1.GLOBAL_ATTRIBUTE6          ,
         cur1.GLOBAL_ATTRIBUTE7          ,
         cur1.GLOBAL_ATTRIBUTE8          ,
         cur1.GLOBAL_ATTRIBUTE9          ,
         cur1.GLOBAL_ATTRIBUTE10         ,
         cur1.GLOBAL_ATTRIBUTE11         ,
         cur1.GLOBAL_ATTRIBUTE12         ,
         cur1.GLOBAL_ATTRIBUTE13         ,
         cur1.GLOBAL_ATTRIBUTE14         ,
         cur1.GLOBAL_ATTRIBUTE15         ,
         cur1.GLOBAL_ATTRIBUTE16         ,
         cur1.GLOBAL_ATTRIBUTE17         ,
         cur1.GLOBAL_ATTRIBUTE18         ,
         cur1.GLOBAL_ATTRIBUTE19         ,
         cur1.GLOBAL_ATTRIBUTE20         ,
         cur1.LAST_UPDATE_LOGIN
       );  
     exception
       when others then
     	 l_err_msg_tmp_1 := sqlerrm;
     end;
     
     for cur2 in cur_line_tl(cur1.org_id, cur1.memo_line_id) loop 
       l_err_msg_tmp_2 := '';
       	  	 
       begin	  	       
         -- import data
         insert into AR_MEMO_LINES_ALL_TL
         (
           MEMO_LINE_ID       ,
           ORG_ID             ,
           NAME               ,
           DESCRIPTION        ,
           LANGUAGE           ,
           SOURCE_LANG        ,
           LAST_UPDATE_DATE   ,
           CREATION_DATE      ,
           CREATED_BY         ,
           LAST_UPDATED_BY    ,
           LAST_UPDATE_LOGIN
         )
         values
         (
           l_memo_line_id          ,
           CAPBRIMP_MAPPING.GetOrgMapping(cur2.ORG_ID),
           cur2.NAME               ,
           cur2.DESCRIPTION        ,
           cur2.LANGUAGE           ,
           cur2.SOURCE_LANG        ,
           cur2.LAST_UPDATE_DATE   ,
           cur2.CREATION_DATE      ,
           CAPBRIMP_MAPPING.GetUserMapping(cur2.CREATED_BY),  
           CAPBRIMP_MAPPING.GetUserMapping(cur2.LAST_UPDATED_BY),
           cur2.LAST_UPDATE_LOGIN
         );  
       exception
         when others then
     	   l_err_msg_tmp_2 := sqlerrm;
       end;
	   
	   if l_err_msg_tmp_2 = '' then
         l_processed_count_2 := l_processed_count_2 + 1;  
       else
         l_rejected_count_2 := l_rejected_count_2 + 1;
         l_err_msg_2 := l_err_msg_tmp_2; 
       end if;
     end loop;
     
     if l_err_msg_tmp_1 = '' then
       l_processed_count_1 := l_processed_count_1 + 1;  
     else
       l_rejected_count_1 := l_rejected_count_1 + 1;
       l_err_msg_1 := l_err_msg_tmp_1; 
     end if;
   end loop;
   
   if l_rejected_count_1 <> 0 and l_rejected_count_2 <> 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPARIMP_MEMO_LINE_B', sysdate, l_processed_count_1, l_rejected_count_1, l_processed_count_1-l_rejected_count_1, l_err_msg_1 );
   
   if l_processed_count_2 = 0 then   
     l_rejected_count_2 := l_rejected_count_2 + 1;
     l_err_msg_2 := 'Auto Accounting Segment not found';
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPARIMP_MEMO_LINE_TL', sysdate, l_processed_count_2, l_rejected_count_2, l_processed_count_2-l_rejected_count_2, l_err_msg_2 );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit